﻿using System.Collections.Generic;
using System.Data.SQLite;
using System.Text;
using System;
using System.Data;

namespace AdsorptionQuerier
{
    class AdsorptionSqlReader : IAdsorptionDataReader
    {
        private SQLiteConnection m_conn;
        public bool HasItemData
        {
            get;set;
        }

        private void Initialize()
        {
            string sCmdText = "SELECT * FROM sqlite_master WHERE type='table'";

            m_conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(sCmdText, m_conn);
            SQLiteDataReader reader = cmd.ExecuteReader();
            HasItemData = false;
            while (reader.Read())
            {
                string sTbName = reader.GetString(1);
                if (sTbName == "itemData")
                {
                    HasItemData = true;
                    break;
                }
            }
            reader.Close();
            m_conn.Close();
        }

        public AdsorptionSqlReader(string sFile)
        {
            string sConn = string.Format("Data Source={0};Pooling=true;FailIfMissing=false", sFile);
            m_conn = new SQLiteConnection(sConn);
            Initialize();
        }

        public IEnumerable<string> GetDataColumns()
        {
            List<string> lsRet = new List<string>();
            lsRet.AddRange(new string[] { "Item", "Weight" });
            if (HasItemData)
            {
                lsRet.AddRange(new string[] { "Item Name", "Release Date", "IMDb Link" });
            }
            return lsRet;
        }

        public IEnumerable<WeightedItem> Read(string sUser, string sItem)
        {
            StringBuilder sCmdQuery = new StringBuilder();

            if (HasItemData)
            {
                sCmdQuery.Append("SELECT weightData.label, weightData.maxWeight, itemData.Name, ");
                sCmdQuery.Append("itemData.ReleaseDate, itemData.IMDb FROM ");
                sCmdQuery.Append("(SELECT label, MAX(weight) AS maxWeight ");
                sCmdQuery.Append("FROM (SELECT label, weight FROM users WHERE users.node = @u ");
                sCmdQuery.Append("UNION SELECT label, weight FROM items WHERE items.node = @i) ");
                sCmdQuery.Append("GROUP BY label ORDER BY maxWeight DESC LIMIT 100) AS weightData, ");
                sCmdQuery.Append("itemData ");
                sCmdQuery.Append("WHERE weightData.label = itemData.Id");
            }
            else
            {
                sCmdQuery.Append("SELECT label, MAX(weight) AS maxWeight ");
                sCmdQuery.Append("FROM (SELECT label, weight FROM users WHERE users.node = @u ");
                sCmdQuery.Append("UNION SELECT label, weight FROM items WHERE items.node = @i) ");
                sCmdQuery.Append("GROUP BY label ORDER BY maxWeight DESC LIMIT 100");
            }

            try
            {
                m_conn.Open();
                
                SQLiteCommand cmd = new SQLiteCommand(sCmdQuery.ToString(), m_conn);
                cmd.Parameters.AddWithValue("u", sUser);
                cmd.Parameters.AddWithValue("i", sItem);
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    WeightedItem wi;
                    if (HasItemData)
                    {
                        wi = new WeightedItem(reader.GetString(0), reader.GetDouble(1),
                            reader.GetString(2), reader.GetString(3), reader.GetString(4));
                    }
                    else
                    {
                        wi = new WeightedItem(reader.GetString(0), reader.GetDouble(1));
                    }
                    yield return wi;
                }
                reader.Close();

            }
            finally
            {
                if (m_conn.State != ConnectionState.Closed)
                {
                    m_conn.Close();
                }
            }
        }

        private IEnumerable<string> GetData(string sSql)
        {
            try
            {
                m_conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(sSql.ToString(), m_conn);
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    yield return reader.GetString(0);
                }
                reader.Close();

            }
            finally
            {
                if (m_conn.State != ConnectionState.Closed)
                {
                    m_conn.Close();
                }
            }
        }
        public IEnumerable<string> GetUsers()
        {

            return GetData("SELECT DISTINCT node FROM users");
            
        }
        public IEnumerable<string> GetItems()
        {
            return GetData("SELECT DISTINCT node FROM items");
        }
    }
}
